# -*- coding: utf-8 -*-
# !/usr/bin/env python

# @Date: 2019-02-27 9:44:12
# @Author: luolisong(luolisong1996@163.com)

# 风险事项管理

import json
import sys

from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib import auth
from lib.table import table
from lib.validform import V, ValidateForm

app = Blueprint(__name__ + "_app", __name__)

reload(sys)
sys.setdefaultencoding('utf8')


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 风险管理表格数据接口
@app.route('/api/riskevents/getdata', methods=['post'])
@auth.permission('risk')
def get_riskEvents_tableData():
    try:
        tb = table()
        g.cursor.execute("""select count(1) as total
                            from (
                                select a.risk_event_id as arisk_event_id,
                              a.risk_event_content as arisk_event_content,
                              a.solution as asolution,
                              a.remark as aremark,
                              a.analysis_id as aanalysis_id,
                              a.exist_update as aexist_update,
                              a.risk_event_name as arisk_event_name,
                              a.risk_event_level as arisk_event_level,
                              a.risk_event_type as arisk_event_type,
                              case when count(distinct g.agency_id)=0 then '-'
                                else count(distinct g.agency_id)||'个机构'||
                                      count(distinct f.info_sys_id)||'个系统'||
                                      count(distinct f.sys_node_id)||'个节点'
                                end as ainfluence,
                              count(e.log_id) as add_sum,
                              d.person_name as ar_person,
                              d2t(a.r_time) as ar_time,
                              a.early_warn as aearly_warn,
                              a.is_commit as ais_commit
                            from h_risk_event_info as a
                            left join h_event_element_info as b on a.risk_event_id = b.risk_event_id
                            left join h_event_element_detail as c on b.element_id = c.element_id
                            left join h_system_node as f on c.sys_id=f.sys_node_id
                            left join h_information_system as g on f.info_sys_id=g.info_sys_id
                            left join h_evemt_element_add as e on b.element_id = e.element_id
                            left join sys_person as d on a.r_person = d.person_id
                            %s
                            group by
                              a.risk_event_id,
                              a.risk_event_content,
                              a.solution,
                              a.remark,
                              a.analysis_id,
                              a.risk_event_name,
                              a.risk_event_level,
                              a.risk_event_type,
                              d.person_name,
                              a.r_time,
                              a.early_warn,
                              a.is_commit,
                              a.exist_update
                            ) a
                        """ % (tb.where(['a.risk_event_name'], "a.state='1'")))
        total = g.cursor.fetchone()['total']
        # 风险事项表格信息限制条件：state = 1 疑问点：系统个数不知准不准确
        g.cursor.execute("""select a.risk_event_id as arisk_event_id,
                              a.risk_event_content as arisk_event_content,
                              a.solution as asolution,
                              a.remark as aremark,
                              a.analysis_id as aanalysis_id,
                              a.exist_update as aexist_update,
                              a.risk_event_name as arisk_event_name,
                              a.risk_event_level as arisk_event_level,
                              a.risk_event_type as arisk_event_type,
                              case when count(distinct g.agency_id)=0 then '-'
                                else count(distinct g.agency_id)||'个机构'||
                                      count(distinct f.info_sys_id)||'个系统'||
                                      count(distinct f.sys_node_id)||'个节点'
                                end as ainfluence,
                              count(e.log_id) as add_sum,
                              d.person_name as ar_person,
                              d2t(a.r_time) as ar_time,
                              a.early_warn as aearly_warn,
                              a.is_commit as ais_commit
                            from h_risk_event_info as a
                            left join h_event_element_info as b on a.risk_event_id = b.risk_event_id
                            left join h_event_element_detail as c on b.element_id = c.element_id
                            left join h_system_node as f on c.sys_id=f.sys_node_id
                            left join h_information_system as g on f.info_sys_id=g.info_sys_id
                            left join h_evemt_element_add as e on b.element_id = e.element_id
                            left join sys_person as d on a.r_person = d.person_id
                            %s
                            group by
                              a.risk_event_id,
                              a.risk_event_content,
                              a.solution,
                              a.remark,
                              a.analysis_id,
                              a.risk_event_name,
                              a.risk_event_level,
                              a.risk_event_type,
                              d.person_name,
                              a.r_time,
                              a.early_warn,
                              a.is_commit,
                              a.exist_update %s %s %s""" % (tb.where(['a.risk_event_name'], "a.state='1'"), tb.orderBy(), tb.offset(), tb.limit()))
        tableData = g.cursor.fetchall()
        # 统计类型个数
        g.cursor.execute("""select risk_event_type,
                                count(risk_event_type)
                            from h_risk_event_info
                            where state='1'
                            group by risk_event_type""")
        echarts = g.cursor.fetchall()
        # 统计风险事项等级个数
        g.cursor.execute("""select coalesce(sum(case when risk_event_level='1' then 1 else 0 end), 0) as "一般",
                            coalesce(sum(case when risk_event_level='2' then 1 else 0 end), 0) as "较大",
                            coalesce(sum(case when risk_event_level='3' then 1 else 0 end), 0) as "重大",
                            coalesce(sum(case when risk_event_level='4' then 1 else 0 end), 0) as "特大"
                          from h_risk_event_info where state='1'""")
        echartsone = g.cursor.fetchone()
        # 15天里风险事项个数变化
        g.cursor.execute("""select case when b.all_risk_cnt is null then 0 else b.all_risk_cnt end as all_risk_cnt,
                                case when b.handle_risk_cnt is null then 0 else b.handle_risk_cnt end as handle_risk_cnt,
                                a.r_time
                            from (
                                WITH RECURSIVE t(r_time) AS (
                                    SELECT date_trunc('hour',CURRENT_TIMESTAMP-interval '1 day')
                                  UNION ALL
                                    SELECT r_time-interval '1 day' FROM t
                                )
                                SELECT substr(d2t(r_time),1,10) as r_time
                                FROM t
                                LIMIT 15
                            )a
                            left join (
                                select riskitem_num as all_risk_cnt,iscommit_cnt as handle_risk_cnt,substr(d2t(static_date),1,10) as r_time
                                from h_riskitem_data
                                order by substr(d2t(static_date),1,10) desc
                                limit 15
                            )b
                            on a.r_time=b.r_time
                            order by a.r_time asc""")
        timeecharts = g.cursor.fetchall()
        # 如今风险事项求和
        g.cursor.execute("""select count(risk_event_id) as sum from h_risk_event_info where state = '1'""")
        sumrisk = g.cursor.fetchone()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "dataSource": {
                    "total": total,
                    "tableData": tableData
                },
                "echarts": echarts,
                "echartsone": echartsone,
                "timeecharts": timeecharts,
                "sumrisk": sumrisk
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", "warn": str(e)})


# 风险事项名称验证
@app.route('/api/riskEvents/riskverify', methods=['post'])
@auth.permission('risk')
def get_riskEvent_addrisk():
    try:
        form = ValidateForm(
            eventid=['风险事项id', V.optional()],
            value=['风险事项名称', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 读取数据库，看是否有名字相同的风险事项
        if data['eventid'] != '0':
            sql = "and risk_event_id != '%s'" % (data['eventid'])
        else:
            sql = ''
        g.cursor.execute("""select risk_event_id
                                from h_risk_event_info
                                where risk_event_name= '%s' and h_risk_event_info.state = '1' %s
                        """ % (data['value'].strip(), sql))
        resule = g.cursor.fetchone()
        if resule:
            return json.dumps({"status": "fail"})
        else:
            return json.dumps({"status": "success"})
    except Exception:
        return json.dumps({"status": "except", "msg": '异常'})


# 风险管理表格新增接口
@app.route('/api/riskEvents/add', methods=['post'])
@auth.permission('risk')
def add_riskEvents_tableData(_currUser):
    try:
        form = ValidateForm(
            riskname=['风险事项名称', V.required()],
            risktype=['风险事项类型', V.required()],
            risklevel=['风险事项等级', V.required()],
            risksolution=['风险事项解决方案', V.optional()],
            riskdescribe=['风险事项描述', V.optional()],
            remark=['风险事项备注', V.optional()],
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        data['personid'] = _currUser['user']['person_id']
        # 新增风险事项
        g.cursor.execute("""insert into h_risk_event_info(risk_event_name,risk_event_type,
                                    risk_event_level,risk_event_content,solution,r_person,r_time,state,exist_update,remark,early_warn,is_commit)
                                   values(%(riskname)s,%(risktype)s,
                                   %(risklevel)s,%(riskdescribe)s,%(risksolution)s,%(personid)s,
                                   now(),'1','0',%(remark)s,'0','0')
                                   returning risk_event_id""", (data))
        g.conn.commit()
        data['eventId'] = g.cursor.fetchone()['risk_event_id']
        # 操作记录流水表记录
        g.cursor.execute("""insert into h_analysis_event_record(source,risk_event_id,risk_event_name,operate,r_person,r_time) values('2',%(eventId)s,
                                   %(riskname)s,'生成风险事项', %(personid)s, now())""", (data))
        g.conn.commit()
        return json.dumps({"status": "success", "msg": "添加成功"})
    except Exception:
        return json.dumps({"status": "except", "msg": '异常'})


# 风险事项编辑接口
@app.route('/api/riskEvents/riskupdate', methods=['post'])
@auth.permission('risk')
def add_riskEvents_riskupdate(_currUser):
    try:
        form = ValidateForm(
            ariskname=['风险事项名称', V.required()],
            risktype=['风险事项类型', V.required()],
            risklevel=['风险事项等级', V.required()],
            risksolution=['风险事项解决方案', V.optional()],
            riskdescribe=['风险事项描述', V.optional()],
            remark=['风险事项备注', V.optional()],
            riskid=['风险事项id', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        data['personid'] = _currUser['user']['person_id']
        g.cursor.execute("""update h_risk_event_info
                                  set risk_event_name = %(ariskname)s, risk_event_type = %(risktype)s,
                                      risk_event_level = %(risklevel)s, risk_event_content = %(riskdescribe)s,
                                      solution = %(risksolution)s,remark = %(remark)s,
                                      submit_time = now(),r_person = %(personid)s
                                  where risk_event_id = %(riskid)s""", (data))
        g.conn.commit()
        # data['eventId'] = g.cursor.fetchone()['risk_event_id']
        return json.dumps({"status": "success", "msg": "修改成功"})
    except Exception, e:
        return json.dumps({"status": "except", "msg": '异常', "warning": str(e)})


# 删除风险事项
@app.route('/api/riskEvents/delete', methods=['post'])
@auth.permission('risk')
def get_riskEvent_delect():
    try:
        form = ValidateForm(
            arisk_event_id=['用户姓名', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute("""update h_risk_event_info set state = '0' where risk_event_id = %(arisk_event_id)s""" % (data))
        g.conn.commit()
        return json.dumps({"status": "success"})
    except Exception:
        return json.dumps({"status": "except", "msg": '异常'})


# 风险详情表格数据接口
@app.route('/api/riskEvents/Derails/getInitil', methods=['post'])
@auth.permission('risk')
def get_riskEventsDerails_tableData():
    try:
        form = ValidateForm(
            id=['风险事项编号', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 基本信息
        g.cursor.execute("""select row_to_json(a) as basics,
                                json_agg(b) as correlation
                                from h_risk_event_info as c
                                left join (
                                    select case when a.analysis_id is not null then '安全分析' else '人工创建' end as risksource,
                                        a.risk_event_id,
                                        a.risk_event_name,
                                        a.risk_event_level,
                                        case when a.analysis_basis is null or a.analysis_basis='' then '' else a.analysis_basis end as analysis_basis,
                                        a.risk_event_type,
                                        case when a.risk_event_content is null or a.risk_event_content='' then '' else a.risk_event_content end as risk_event_content,
                                        case when a.remark is null or a.remark='' then '' else a.remark end as remark,
                                        a.r_time,
                                        d.person_name as r_person,
                                        case when a.solution is null or a.solution='' then '' else a.solution end as solution,
                                        is_commit
                                    from h_risk_event_info as a
                                    left join sys_person as d
                                    on a.r_person = d.person_id
                                ) as a on c.risk_event_id = a.risk_event_id
                                left join (
                                select
                                    a.risk_event_id,
                                    c.event_name,
                                    c.threaten_level,
                                    c.event_desc
                                    from
                                    h_risk_event_info as a
                                    left join h_analysis_info as b on a.analysis_id = b.analysis_id
                                    left join h_attack_eventinfo as c on b.event_id = c.event_id
                                )as b on c.risk_event_id = b.risk_event_id
                                where c.risk_event_id = %(id)s
                                group by a""", data)
        dataSource = g.cursor.fetchone()
        dataSource['iscommit'] = dataSource['basics']['is_commit']
        # 相关安全事件和相关情报事件
        g.cursor.execute("""select a.element_name,
                                case when string_agg(distinct e.sys_node_name,',') is not null then string_agg(distinct e.sys_node_name,',') else '-' end as effect_sys_node,
                                case when string_agg(distinct f.info_sys_name,',') is not null then string_agg(distinct f.info_sys_name,',') else '-' end as effect_info_sys,
                                case when string_agg(distinct g.agency_name,',') is not null then string_agg(distinct g.agency_name,',') else '-' end as effect_agency,
                                coalesce(json_agg(distinct c) filter (where c.id is not null), '[]') as history,
                                coalesce(json_agg(distinct d) filter (where d.id is not null), '[]') as attack
                            from h_event_element_info as a
                            left join h_event_element_detail as b
                            on a.element_id = b.element_id
                            left join h_system_node as e
                            on b.sys_id = e.sys_node_id
                            left join h_information_system as f
                            on f.info_sys_id = e.info_sys_id
                            left join sys_agency_info as g
                            on g.agency_id = f.agency_id
                            left join (
                                select distinct id, a.risk_event_id, a.related_element_name,
                                    b.risk_event_name,
                                    b.risk_event_level,
                                    b.risk_event_type,
                                    case when b.risk_event_content is null or b.risk_event_content='' then '-' else b.risk_event_content end as risk_event_content
                                    from h_event_related_analysis_info as a
                                    left join h_risk_event_info as
                                    b on b.risk_event_id::varchar = a.related_id
                                where a.related_type = '3'
                            ) as c
                            on c.related_element_name = a.element_name and a.risk_event_id = c.risk_event_id
                            left join (
                                select distinct a.id,a.risk_event_id, a.related_element_name,
                                    b.event_name as risk_event_name,
                                    b.attack_num,
                                    d2t(b.start_time) as start_time,
                                    d2t(b.new_time) as new_time,
                                    b.threaten_level as risk_event_level,
                                    case when b.event_desc is null or b.event_desc='' then '-' else b.event_desc end as risk_event_content
                                from
                                    h_event_related_analysis_info as a
                                left join h_attack_eventinfo as b
                                    on b.event_id = a.related_id
                                where a.related_type = '4'
                            ) as d
                            on d.related_element_name = a.element_name and a.risk_event_id = d.risk_event_id
                            where a.risk_event_id = %(id)s
                            group by a.element_name""", data)
        correlation = g.cursor.fetchall()
        # 操作记录流水表
        g.cursor.execute("""select a.risk_event_name,
                                b.risk_event_type,
                                case when a.source='1' then '创建风险事项'
                                     when a.operate='生成风险事项' then '创建风险事项'
                                     when a.operate='风险事项提交' then '提交到风险预警'
                                else a.operate end as operate,
                                case when b.risk_event_level = '1' then '一般'
                                    when b.risk_event_level = '2' then '较大'
                                    when b.risk_event_level = '3' then '重大'
                                    when b.risk_event_level = '4' then '特大' end as risk_event_level,
                                d.person_name as r_person,
                                d2t(a.r_time) as r_time
                            from
                            h_analysis_event_record as a
                            left join h_risk_event_info as b on a.risk_event_id::integer = b.risk_event_id
                            left join sys_person as d on a.r_person = d.person_id
                            where a.risk_event_id::varchar = %(id)s
                            order by a.r_time desc""", data)
        operate = g.cursor.fetchall()

        return json.dumps({
            "status": "success",
            "data": {
                "dataSource": dataSource,
                "correlation": correlation,
                "operate": operate
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", "msg": '异常', "warn": str(e)})


# 风险详情表格数据接口归入数据接口
@app.route('/api/riskEvents/guiru', methods=['post'])
@auth.permission('risk')
def guiru():
    try:
        form = ValidateForm(
            arisk_event_id=['风险事项编号', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 查询新增影响网站
        g.cursor.execute("""select a.element_id, a.log_id, a.log_type, d2t(a.ts) as ts, a.sys_id, a.sys_name, a.log_level,
                                a.unit_id, a.raw_msg, a.sip, a.sport, a.sip_city, a.sip_country, a.dip, a.dport,
                                a.dip_city, a.dip_country, a.proto, a.response_action, a.url, a.tag, a.event_desc,
                                a.md5s, a.behavior_name_level, a.malname, a.virus_type, a.virus_family, a.
                                virus_behavior, a.control_content, a.dev_name, a.dev_id, a.action, a.cve_id,
                                a.vul_name, a.vul_type, a.client_ip, a.operate_user, a.result, a.action_state,
                                a.flow_file_type, a.flow_data_detail, a.final_result, a.is_malicious,
                                a.file_type, a.network_monitor, a.disposal_state
                            from h_evemt_element_add a
                            left join h_event_element_info b
                            on a.element_id=b.element_id
                            where a.is_add = '0' and b.risk_event_id=%(arisk_event_id)s""", data)
        dataSource = g.cursor.fetchall()

        return json.dumps({
            "status": "success",
            "data": {
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", "msg": '异常'})


# 风险事项选择新增影响范围归入原有风险事项
@app.route('/api/riskEvent/guiruone', methods=['post'])
@auth.permission('risk')
def guiruone():
    try:
        form = ValidateForm(
            log_id=['日志唯一标识', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 通过对应日志id将数据插入日志表
        g.cursor.execute("""insert into h_event_element_detail(
                          element_id,log_id,log_type,ts,sys_id,sys_name,
                          log_level,unit_id,raw_msg,sip,sport,sip_city,
                          sip_country,dip,dport,dip_city,dip_country,
                          proto,response_action,url,tag,event_desc,
                          md5s,behavior_name_level,malname,virus_type,
                          virus_family,virus_behavior,control_content,
                          dev_name,dev_id,action,cve_id,vul_name,vul_type,
                          client_ip,operate_user,result,action_state,
                          flow_file_type,flow_data_detail,final_result,
                          is_malicious,file_type,network_monitor,disposal_state,
                          r_time
                       )  select element_id,log_id,log_type,ts,sys_id,sys_name,
                          log_level,unit_id,raw_msg,sip,sport,sip_city,
                          sip_country,dip,dport,dip_city,dip_country,
                          proto,response_action,url,tag,event_desc,
                          md5s,behavior_name_level,malname,virus_type,virus_family,
                          virus_behavior,control_content,dev_name,dev_id,action,
                          cve_id,vul_name,vul_type,client_ip,operate_user,
                          result,action_state,flow_file_type,flow_data_detail,
                          final_result,is_malicious,file_type,network_monitor,
                          disposal_state,r_time
                         from
                           h_evemt_element_add where log_id = %(log_id)s
                        returning element_id""", data)
        g.conn.commit()
        data['element_id'] = g.cursor.fetchone()['element_id']
        # 通过element_id更新影响范围
        g.cursor.execute("""update h_event_effect_info
                       set sys_node_num = a.sys_node_num,effect_sys_node = a.effect_sys_node,info_sys_num = a.info_sys_num,
                       effect_info_sys = a.effect_info_sys, agency_num = a.agency_num,effect_agency = a.effect_agency,
                       r_time = now()
                       from(
                       select
                            count(a.sys_id) as sys_node_num,
                            string_agg(a.sys_id||'' , ',')as effect_sys_node,
                            count(c.info_sys_id) as info_sys_num,
                            string_agg(c.info_sys_id||'' , ',')as effect_info_sys,
                            count(b.agency_id) as agency_num,
                            string_agg(b.agency_id||'' , ',')as effect_agency
                        from
                            h_event_element_detail as a
                            left join h_system_node as b on a.sys_id = b.sys_node_id
                            left join h_information_system as c on b.info_sys_id = c.info_sys_id
                            where element_id = %(element_id)s
                            group by a.element_id
                       ) as a
                       where element_id = %(element_id)s""", data)
        g.conn.commit()
        # 更新新增要要素表
        g.cursor.execute("""update h_evemt_element_add
                             set is_add = '1'
                            where log_id = %(log_id)s""", data)
        g.conn.commit()
        # 判断是否还存在更新以更新风险事项表
        g.cursor.execute("""select c.log_id
                                from
                                    h_event_element_info as a
                                left join h_event_element_info as b on  a.risk_event_id = b.risk_event_id
                                left join h_evemt_element_add as c on b.element_id = c.element_id
                                where a.element_id = %(element_id)s and c.is_add = '0'""", data)
        resule = g.cursor.fetchone()
        if resule:
            pass
        else:
            # 更新风险事项是否存在更新
            g.cursor.execute(""" update h_risk_event_info
                                    set exist_update = '0'
                                    where risk_event_id = (
                                            select risk_event_id
                                        from
                                        h_event_element_info
                                        where element_id = %(element_id)s
                                    )""", data)
            g.conn.commit()
        return json.dumps({
            "status": "success"
        })
    except Exception:
        return json.dumps({
            "status": "except", "msg": '异常'
        })


# 风险事项提交到预警
@app.route('/api/riskEvents/risktijiao', methods=['post'])
@auth.permission('risk')
def get_riskEvent_risktijiao(_currUser):
    try:
        form = ValidateForm(
            arisk_event_id=['风险事项id', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        data['personid'] = _currUser['user']['person_id']

        # 改变风险事项表的提交状态，以及提交人和提交时时间
        g.cursor.execute("""update h_risk_event_info set is_commit = '1',submit_time = now(),submit_person = %(personid)s where risk_event_id = %(arisk_event_id)s""" % (data))
        g.conn.commit()

        # 返回风险事项名称为操作流水记录表做准备
        g.cursor.execute("""select risk_event_name from h_risk_event_info where risk_event_id = %(arisk_event_id)s""" % (data))
        data['riskname'] = g.cursor.fetchone()['risk_event_name']

        # 操作记录流水表记录
        g.cursor.execute("""insert into h_analysis_event_record(source,risk_event_id,risk_event_name,operate,r_person,r_time) values('2',%(arisk_event_id)s,
                                   %(riskname)s,'风险事项提交', %(personid)s, now())""", (data))
        g.conn.commit()

        return json.dumps({"status": "success"})
    except Exception, e:
        return json.dumps({"status": "except", "msg": '异常', "warn": str(e)})


# 风险详情表格数据接口
@app.route('/api/riskEvents/Derails/larmlog', methods=['post'])
@auth.permission('warning')
def get_riskEventsDerails_larmlog():
    try:
        form = ValidateForm(
            id=['风险事项编号', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        data['dataSource'] = {
            "statistical": {},  # 摘要信息
            "warninfos": []  # 告警信息（机构归并）
        }
        # 附录摘要信息统计
        g.cursor.execute("""select
                              a.risk_event_id,
                              coalesce(count(c.element_id),0) as sumelement,
                              coalesce(sum(c.sys_node_num),0) as sys_node_num,
                              coalesce(sum(c.info_sys_num),0) as info_sys_num
                           from h_risk_event_info as a
                           left join h_event_element_info as b on a.risk_event_id = b.risk_event_id
                           left join h_event_effect_info as c on b.element_id = c.element_id
                           where a.risk_event_id = %(id)s
                           group by
                               a.risk_event_id """, data)
        data['dataSource']['statistical'] = g.cursor.fetchone()
        # 获取对应风险事项下的要素id
        g.cursor.execute("""select a.element_id,a.element_name,
                                coalesce(json_agg(b) filter (where b.log_id is not null), '[]')as logs
                            from h_event_element_info a
                            left join (
                                select a.element_id, a.log_id, a.log_type, d2t(a.ts) as ts, a.sys_id, a.sys_name, a.log_level,
                                    a.unit_id, a.raw_msg, a.sip, a.sport, a.sip_city, a.sip_country, a.dip, a.dport,
                                    a.dip_city, a.dip_country, a.proto, a.response_action, a.url, a.tag, a.event_desc,
                                    a.md5s, a.behavior_name_level, a.malname, a.virus_type, a.virus_family, a.
                                    virus_behavior, a.control_content, a.dev_name, a.dev_id, a.action, a.cve_id,
                                    a.vul_name, a.vul_type, a.client_ip, a.operate_user, a.result, a.action_state,
                                    a.flow_file_type, a.flow_data_detail, a.final_result, a.is_malicious,
                                    a.file_type, a.network_monitor, a.disposal_state
                                from h_event_element_detail a
                                left join h_event_element_info b
                                on a.element_id=b.element_id
                            ) b
                            on a.element_id=b.element_id
                            where a.risk_event_id = %(id)s
                            group by a.element_id,a.element_name""", data)
        data['dataSource']['warninfos'] = g.cursor.fetchall()
        data['dataSource']['len'] = len(data['dataSource']['warninfos'])
        return json.dumps({
            "status": "success",
            "data": data
        })
    except Exception, e:
        return json.dumps({"status": "except", "msg": '异常', "warn": str(e)})
